/*
 Navicat Premium Data Transfer

 Source Server         : 监所
 Source Server Type    : PostgreSQL
 Source Server Version : 90204 (90204)
 Source Host           : 47.108.95.57:15432
 Source Catalog        : sjyy
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 90204 (90204)
 File Encoding         : 65001

 Date: 22/10/2025 16:16:36
*/


-- ----------------------------
-- Table structure for case_info
-- ----------------------------
DROP TABLE IF EXISTS "public"."case_info";
CREATE TABLE "public"."case_info" (
  "id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "case_number" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
  "occur_time" timestamp(6) NOT NULL,
  "location" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "description" text COLLATE "pg_catalog"."default" NOT NULL,
  "police_station" varchar(50) COLLATE "pg_catalog"."default",
  "police_bureau" varchar(50) COLLATE "pg_catalog"."default",
  "phone" varchar(20) COLLATE "pg_catalog"."default",
  "process_result" text COLLATE "pg_catalog"."default",
  "case_type" varchar(50) COLLATE "pg_catalog"."default",
  "case_subtype" varchar(50) COLLATE "pg_catalog"."default",
  "case_small_type" varchar(50) COLLATE "pg_catalog"."default",
  "keywords" varchar(200) COLLATE "pg_catalog"."default",
  "incident_reason" text COLLATE "pg_catalog"."default",
  "time_period" varchar(20) COLLATE "pg_catalog"."default",
  "river_section" varchar(50) COLLATE "pg_catalog"."default",
  "location_detail" varchar(100) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) NOT NULL DEFAULT pg_systimestamp(),
  "update_by" varchar(32) COLLATE "pg_catalog"."default",
  "update_time" timestamp(6),
  "del_flag" int4 DEFAULT 0,
  "status" int4 NOT NULL DEFAULT 0,
  "create_by" varchar(32) COLLATE "pg_catalog"."default",
  "occur_year" int4,
  "check_person" varchar(32) COLLATE "pg_catalog"."default",
  "check_person_id" varchar(32) COLLATE "pg_catalog"."default",
  "occur_year_month" varchar(7) COLLATE "pg_catalog"."default",
  "create_by_name" varchar COLLATE "pg_catalog"."default",
  "co_examine_department" varchar(100) COLLATE "pg_catalog"."default",
  "co_dispose_department" varchar(100) COLLATE "pg_catalog"."default",
  "is_public_water_area" int4,
  "is_chargeback" int4 NOT NULL DEFAULT 0,
  "source" int4,
  "serial_number" varchar(100) COLLATE "pg_catalog"."default",
  "is_new_water_sport" int4
)
;
ALTER TABLE "public"."case_info" OWNER TO "guass";
COMMENT ON COLUMN "public"."case_info"."id" IS '主键ID';
COMMENT ON COLUMN "public"."case_info"."case_number" IS '警情编号';
COMMENT ON COLUMN "public"."case_info"."occur_time" IS '警情时间';
COMMENT ON COLUMN "public"."case_info"."location" IS '发生地点';
COMMENT ON COLUMN "public"."case_info"."description" IS '警情内容';
COMMENT ON COLUMN "public"."case_info"."police_station" IS '直接出警单位';
COMMENT ON COLUMN "public"."case_info"."police_bureau" IS '所属分局';
COMMENT ON COLUMN "public"."case_info"."phone" IS '报警电话';
COMMENT ON COLUMN "public"."case_info"."process_result" IS '处理结果';
COMMENT ON COLUMN "public"."case_info"."case_type" IS '警情类型';
COMMENT ON COLUMN "public"."case_info"."case_subtype" IS '警情细类';
COMMENT ON COLUMN "public"."case_info"."case_small_type" IS '警情子类';
COMMENT ON COLUMN "public"."case_info"."keywords" IS '警情关键字';
COMMENT ON COLUMN "public"."case_info"."incident_reason" IS '涉事原因';
COMMENT ON COLUMN "public"."case_info"."time_period" IS '时段(如22:00-24:00)';
COMMENT ON COLUMN "public"."case_info"."river_section" IS '江段流域';
COMMENT ON COLUMN "public"."case_info"."location_detail" IS '场所部位';
COMMENT ON COLUMN "public"."case_info"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."case_info"."update_by" IS '更新人';
COMMENT ON COLUMN "public"."case_info"."update_time" IS '更新时间';
COMMENT ON COLUMN "public"."case_info"."del_flag" IS '状态(0:存在 1:删除)';
COMMENT ON COLUMN "public"."case_info"."status" IS '状态(0:非锁定 1:锁定)';
COMMENT ON COLUMN "public"."case_info"."create_by" IS '录入人员';
COMMENT ON COLUMN "public"."case_info"."occur_year" IS '警情发生年份';
COMMENT ON COLUMN "public"."case_info"."check_person" IS '核查人';
COMMENT ON COLUMN "public"."case_info"."check_person_id" IS '核查人id';
COMMENT ON COLUMN "public"."case_info"."occur_year_month" IS '警情发生年月(yyyy-MM)';
COMMENT ON COLUMN "public"."case_info"."co_examine_department" IS '协查通报部门';
COMMENT ON COLUMN "public"."case_info"."co_dispose_department" IS '协同处置部门';
COMMENT ON COLUMN "public"."case_info"."is_public_water_area" IS '是否公共水域';
COMMENT ON COLUMN "public"."case_info"."is_chargeback" IS '是否退单警情 0-否 1-是';
COMMENT ON COLUMN "public"."case_info"."source" IS '案件来源0自建,1导入';
COMMENT ON COLUMN "public"."case_info"."serial_number" IS '案件序号,导入时使用';
COMMENT ON COLUMN "public"."case_info"."is_new_water_sport" IS '是否新兴水上运动:否,1:是';

-- ----------------------------
-- Indexes structure for table case_info
-- ----------------------------
CREATE INDEX "idx_case_info_del_flag_create_time_1_1" ON "public"."case_info" USING btree (
  "del_flag" "pg_catalog"."int4_ops" ASC NULLS LAST,
  "create_time" "pg_catalog"."timestamp_ops" DESC NULLS FIRST
);
CREATE INDEX "idx_case_info_del_flag_occur_time_1_1" ON "public"."case_info" USING btree (
  "del_flag" "pg_catalog"."int4_ops" ASC NULLS LAST,
  "occur_time" "pg_catalog"."timestamp_ops" ASC NULLS LAST
);

-- ----------------------------
-- Uniques structure for table case_info
-- ----------------------------
ALTER TABLE "public"."case_info" ADD CONSTRAINT "idx_case_info_case_number_1_1" UNIQUE ("case_number");

-- ----------------------------
-- Primary Key structure for table case_info
-- ----------------------------
ALTER TABLE "public"."case_info" ADD CONSTRAINT "case_info_pkey_1_1" PRIMARY KEY ("id");
